package com.hst.capacity.application.export.impl;

import cn.hutool.core.date.DatePattern;
import com.alibaba.fastjson2.JSON;
import com.hst.capacity.application.biz.FranchiseeBiz;
import com.hst.capacity.application.biz.OrderBiz;
import com.hst.capacity.application.export.XssfExporter;
import com.hst.capacity.domain.common.constants.Constants;
import com.hst.capacity.domain.common.constants.HashMapConstants;
import com.hst.capacity.domain.model.entity.order.express.ExpressOrderEntity;
import com.hst.capacity.domain.model.request.export.OrderExportParam;
import com.hst.capacity.domain.model.request.finance.OrderStatementParam;
import com.hst.capacity.domain.model.request.invoice.InvoiceLstParam;
import com.hst.capacity.domain.model.response.export.ExportOrderVO;
import com.hst.capacity.domain.model.response.export.JourneyVO;
import com.hst.capacity.domain.model.response.finance.OrderStatementVO;
import com.hst.capacity.domain.model.response.invoice.InvoiceVO;
import com.hst.capacity.domain.service.InvoiceService;
import com.hst.capacity.infra.persistence.jpa.order.express.ExOrderRepository;
import com.hst.capacity.infra.persistence.mybatis.AccountMapper;
import com.hst.capacity.infra.persistence.mybatis.OrderMapper;
import com.hst.capacity.infra.util.FileUtil;
import com.hst.capacity.infra.util.NumUtil;
import com.hst.capacity.infra.util.StringUtil;
import io.lettuce.core.dynamic.annotation.Param;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FileUtils;
import org.apache.ibatis.session.SqlSession;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.stereotype.Service;
import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.text.DecimalFormat;
import java.time.LocalDateTime;
import java.util.*;

@Slf4j
@Service
public class XssfExporterImpl implements XssfExporter {

    @Value("${templates.excel.invoice}")
    private String invoiceTemplateList;
    @Value("${templates.excel.accountOrder}")
    private String accountOrderTemplateList;
    @Autowired
    OrderMapper orderMapper;
    @Autowired
    OrderBiz orderBiz;
    @Autowired
    InvoiceService invoiceService;
    @Autowired
    private FranchiseeBiz franchiseeBiz;
    @Autowired
    private AccountMapper accountMapper;

    @Data
    private class AccountMatch
    {
        private String nmFranchisee;
        private String cityFranchisee;
        private int rowCount=0;
        private String formulaForMoney;
    }

    @Override
    public Workbook exportOrderList(OrderExportParam param) {

        log.info("OrderExportParam :{}",JSON.toJSONString(param));

        Workbook wb = new XSSFWorkbook();
        String sheetName = "";

        if (param.getModuleType() == null)
            param.setModuleType(Constants.OrderModuleType.SPECIAL);

        switch (param.getModuleType()) {
            case Constants.OrderModuleType.SPECIAL:
                sheetName = "专车订单信息";
                break;
            case Constants.OrderModuleType.INTERCITY:
                sheetName = "城际订单信息";
                break;
            case Constants.OrderModuleType.EXPRESS:
                sheetName = "快车订单信息";
                break;
        }

        Sheet sheet = wb.createSheet(sheetName);

        // 表体——数据列表
        List<ExportOrderVO> list = orderBiz.findAllExportOrderListData(param);

        if (list.size()>0){

            //单元格设置为数值类型，保留2位小数
            CellStyle styleMoney = wb.createCellStyle();
            //styleMoney.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00_ "));//下划线后面的空格不能省略
            styleMoney.setDataFormat(wb.createDataFormat().getFormat("0.00"));

            //单元格设置为整数
            CellStyle styleQty = wb.createCellStyle();
            //styleQty.setDataFormat(HSSFDataFormat.getBuiltinFormat("0_ "));
            styleQty.setDataFormat(wb.createDataFormat().getFormat("0"));

            setOrderExcelSheet(sheet,list,styleMoney,styleQty);
        }

        return wb;
    }

    @Override
    public Workbook exportJourneyList(String idInvoice) {

        Workbook wb = new XSSFWorkbook();
        String sheetName = "行程单";
        Sheet sheet = wb.createSheet(sheetName);
        int iRow = 0;
        int iCol = 0;
        List<String> detailHeaders = new ArrayList<>();
        detailHeaders = Arrays.asList("序号", "订单类型", "车型", "用车时间", "所在城市", "起点/终点",  "支付方式金额", "可开票金额");

        List<JourneyVO> list = orderMapper.findExportJourneyListData(idInvoice);

        if (list.size()==0){
            return wb;
        }
        float iSum = 0;
        for (int i = 0; i < list.size(); i++) {
            iSum+=list.get(i).getPay_money();
        }
        String formattedTravelSum = String.format("%d次行程，合计%.2f元", list.size(), iSum);
        System.out.println(formattedTravelSum);

        // 页眉 -------------------------------------------------
        Header header = sheet.getHeader();
        header.setLeft("阿玛丁");
        header.setRight("1/1");

        // header部----------------------------------------------
        // 标题
        int iRowTitle = iRow;
        Row fixRow_Title = sheet.createRow(iRowTitle);
        Cell cellTitle = fixRow_Title.createCell(0);
        cellTitle.setCellValue("行程单");
        CellStyle styleTitle = wb.createCellStyle();
        styleTitle.setAlignment(HorizontalAlignment.CENTER);
        styleTitle.setVerticalAlignment(VerticalAlignment.CENTER);
        Font font = wb.createFont();
        font.setBold(true);
        font.setFontName("宋体");
        //font.setFontHeight((short) 15);
        font.setFontHeightInPoints((short) 15);
        styleTitle.setFont(font);
        //styleTitle.setBorderBottom(BorderStyle.THIN);
        cellTitle.setCellStyle(styleTitle);
        CellRangeAddress regionTitle = new CellRangeAddress(iRowTitle,iRowTitle,0,detailHeaders.size()-1);
        sheet.addMergedRegion(regionTitle);

        iRow += 1;

        // 申请日期----------------------------------------------
        int iRowAskDate = iRow;
        int iColAskDate = 3;
        Row fixRow_AskDate = sheet.createRow(iRowAskDate);
        Cell cellAskDate = fixRow_AskDate.createCell(0);
        cellAskDate.setCellValue("申请日期：");
        CellRangeAddress regionAskDate = new CellRangeAddress(iRowAskDate,iRowAskDate,0,iColAskDate-1);
        sheet.addMergedRegion(regionAskDate);

        Cell cellAskDateData = fixRow_AskDate.createCell(iColAskDate);
        cellAskDateData.setCellValue(list.get(0).getAsk_date());

        iRow += 1;

        // 用车人手机号----------------------------------------------
        int iRowAskPhone = iRow;
        int iColAskPhone = 3;
        Row fixRow_AskPhone = sheet.createRow(iRowAskPhone);
        Cell cellAskPhone = fixRow_AskPhone.createCell(0);
        cellAskPhone.setCellValue("用车人手机号：");
        CellRangeAddress regionAskPhone = new CellRangeAddress(iRowAskPhone,iRowAskPhone,0,iColAskPhone-1);
        sheet.addMergedRegion(regionAskPhone);

        Cell cellAskPhoneData = fixRow_AskPhone.createCell(iColAskPhone);
        cellAskPhoneData.setCellValue(list.get(0).getPassenger_mobile());

        iRow += 1;

        // 行程总计----------------------------------------------
        int iRowTravelSum = iRow;
        int iColTravelSum = 3;
        Row fixRow_TravelSum = sheet.createRow(iRowTravelSum);
        Cell cellTravelSum = fixRow_TravelSum.createCell(0);
        cellTravelSum.setCellValue("行程总计：");
        CellRangeAddress regionTravelSum = new CellRangeAddress(iRowTravelSum,iRowTravelSum,0,iColTravelSum-1);
        sheet.addMergedRegion(regionTravelSum);

        Cell cellTravelSumData = fixRow_TravelSum.createCell(iColTravelSum);
        cellTravelSumData.setCellValue(formattedTravelSum);

        iRow += 1;

        // 表头——标题行
        Row row = sheet.createRow(iRow);

        // 标题行的单元格设置值
        for (int i = 0; i < detailHeaders.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(detailHeaders.get(i));
            CellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            cell.setCellStyle(style);
        }
        int baseWith = 500;
        int plusWith = 200;
        iCol=-1;
        iCol +=1; sheet.setColumnWidth(iCol,baseWith * 2 + plusWith);
        iCol +=1; sheet.setColumnWidth(iCol,baseWith * 6 + plusWith);
        iCol +=1; sheet.setColumnWidth(iCol,baseWith * 4 + plusWith);
        iCol +=1; sheet.setColumnWidth(iCol,baseWith * 10 + plusWith);
        iCol +=1; sheet.setColumnWidth(iCol,baseWith * 6 + plusWith);
        iCol +=1; sheet.setColumnWidth(iCol,baseWith * 20 + plusWith);
        iCol +=1; sheet.setColumnWidth(iCol,baseWith * 15 + plusWith);
        iCol +=1; sheet.setColumnWidth(iCol,baseWith * 8 + plusWith);

        iRow += 1;
        //detailHeaders = Arrays.asList("序号", "订单类型", "车型", "用车时间", "所在城市", "起点/终点",  "支付方式金额", "可开票金额");
        // 表体——数据列表
        for (int i = 0; i < list.size(); i++) {

            list.get(i).setPayment_type_name(Constants.Payment_Type.get(list.get(i).getPayment_type()));

            String travelInfo = String.format("%s-%s", list.get(i).getStart_address(), list.get(i).getEnd_address());
            String paymentInfo = String.format("%s:%.2f元", list.get(i).getPayment_type_name(), list.get(i).getPay_money());
            String payInfo = String.format("%.2f元", list.get(i).getPay_money());
            // 注意从第二行开始
            row = sheet.createRow(i + iRow);
            // 单元格设置值，暂时先一个一个设吧
            int col = -1;
            col +=1; row.createCell(col).setCellValue((i+1));
            col +=1; row.createCell(col).setCellValue(list.get(i).getOrder_type());
            col +=1; row.createCell(col).setCellValue(list.get(i).getVehicle_type_name());
            col +=1; row.createCell(col).setCellValue(list.get(i).getService_time());
            col +=1; row.createCell(col).setCellValue(list.get(i).getCity_name());
            col +=1; row.createCell(col).setCellValue(travelInfo);
            col +=1; row.createCell(col).setCellValue(paymentInfo);
            col +=1; row.createCell(col).setCellValue(payInfo);

        }
        return wb;
    }

    @Override
    public Workbook exportInvoiceList(InvoiceLstParam param) {

        int iRow = 0;
        int iCol = 0;

        File temp = new File(invoiceTemplateList);
        String dir = "/opt/smartravel/data/excel/invoice/";
        //dir = "D:\\00_Src\\del\\account\\";
        String fileName = dir + LocalDateTime.now().format(DatePattern.PURE_DATETIME_MS_FORMATTER)+".xlsx";
        File file = new File(fileName);

        try {
            if (!file.exists()) {
                if (!file.getParentFile().exists()) {
                    file.mkdirs();
                }
                file.createNewFile();
            }

            FileUtils.copyFile(temp, file);
            //Files.copy(temp.toPath(), file.toPath());

            Workbook wb = new XSSFWorkbook(file);
            Sheet sheet = wb.getSheetAt(0);

            List<InvoiceVO> list = invoiceService.findAllInvoiceLst(param);
            //log.info("InvoiceVO:{}", JSON.toJSONString(list));
            if (list.size()==0) {
                return wb;
            }

            Row row = null;
            iRow = 3;

            // 表体——数据列表
            for (int i = 0; i < list.size(); i++) {
                String invoiceType = "026";         //发票类型:007/026
//                if (1==list.get(i).getIsElecInvoice()){
//                    invoiceType="026";
//                }
                String companyInfo = String.format("%s-%s", list.get(i).getCompanyAddress(), list.get(i).getCompanyPhone());
                String companyAccount = String.format("%s-%s", list.get(i).getCompanyBank(), list.get(i).getCompanyBankNo());

                // 注意从第二行开始
                row = sheet.createRow(i + iRow);
                // 单元格设置值，暂时先一个一个设吧
                int col = -1;
                col +=1; row.createCell(col).setCellValue(list.get(i).getIdInvoice());
                col +=1; row.createCell(col).setCellValue(list.get(i).getIdOrder());
                col +=1; row.createCell(col).setCellValue(list.get(i).getExternalOrderId());
                col +=1; row.createCell(col).setCellValue(list.get(i).getFranchiseeName());
                col +=1; row.createCell(col).setCellValue(HashMapConstants.idFranchiseeCityMap.get(list.get(i).getIdFranchisee()));
                //流水号
                col +=1; row.createCell(col).setCellValue(HashMapConstants.idBrandMap.get(list.get(i).getIdBrandHolder()));
                //发票类型
                col +=1; row.createCell(col).setCellValue(invoiceType);
                //特殊票种
                col +=1; row.createCell(col).setCellValue(list.get(i).getSpecialType());
                //购方名称
                col +=1; row.createCell(col).setCellValue(list.get(i).getInvoiceTitle());
                //购方税号
                col +=1; row.createCell(col).setCellValue(list.get(i).getTaxNo());
                //购方地址、电话
                col +=1; row.createCell(col).setCellValue(companyInfo);
                //购方开户行、账号
                col +=1; row.createCell(col).setCellValue(companyAccount);
                //手机号码
                col +=1; row.createCell(col).setCellValue(list.get(i).getInvoiceReceivePhone());
                //客户邮箱
                col +=1; row.createCell(col).setCellValue(list.get(i).getInvoiceSendEmail());
                //备注
                col +=1; row.createCell(col).setCellValue(list.get(i).getMemo());
                //商品名称
                col +=1; row.createCell(col).setCellValue(list.get(i).getInvoiceContent());
                //税率
                col +=1; row.createCell(col).setCellValue(list.get(i).getTaxRate());
                //规格型号
                col +=1; row.createCell(col).setCellValue("");
                //单位
                col +=1; row.createCell(col).setCellValue("");
                //数量
                col +=1; row.createCell(col).setCellValue("");
                //单价
                col +=1; row.createCell(col).setCellValue("");
                //金额
                col +=1; row.createCell(col).setCellValue(list.get(i).getInvoiceAmount()/100);
                //折扣金额
                col +=1; row.createCell(col).setCellValue("");
                //税收商品编码
                col +=1; row.createCell(col).setCellValue("");
                //免税类型
                col +=1; row.createCell(col).setCellValue("");
                //优惠政策名称
                col +=1; row.createCell(col).setCellValue("");
                //收款人
                col +=1; row.createCell(col).setCellValue(list.get(i).getInvoiceReceiveName());
                //复核人
                col +=1; row.createCell(col).setCellValue(list.get(i).getReviewer());
                //开票人
                col +=1; row.createCell(col).setCellValue(list.get(i).getDrawer());
            }

            return wb;
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }

        return null;
    }

    /**
     * 对账单生成
     * @param moduleType
     * @return
     */
    @Override
    public Workbook exportAccountOrderList(int moduleType) {

        File temp = new File(accountOrderTemplateList);
        String dir = "/opt/smartravel/data/excel/account/";
        //dir = "D:\\00_Src\\del\\account\\";
        String fileName = dir + LocalDateTime.now().format(DatePattern.PURE_DATETIME_MS_FORMATTER)+".xlxs";
        File file = new File(fileName);

        try {
            if (!file.exists()) {
                if (!file.getParentFile().exists()) {
                    file.mkdirs();
                }
                file.createNewFile();
            }

            FileUtils.copyFile(temp, file);

            Workbook wb = new XSSFWorkbook(file);

            List<AccountMatch> listNormal = new ArrayList<>();
            List<AccountMatch> listCancel = new ArrayList<>();
            List<AccountMatch> listHistory = new ArrayList<>();

            for (Map.Entry<String, String> fEntry : HashMapConstants.idFranchiseeCityMap.entrySet()) {
                System.out.println("Key = " + fEntry.getKey() + ", Value = " + fEntry.getValue());
                OrderExportParam param = new OrderExportParam();
                param.setIdFranchisee(fEntry.getKey());
                param.setModuleType(moduleType);
                setExcelSheetByFranchisee(wb,fEntry.getValue(),param,listNormal,listCancel,listHistory);
            }

            if (listNormal.size()>0){
                setMainSheet(wb,listNormal);
            }

            int stepRow=0;
            Sheet sheetRef = wb.getSheetAt(1);//参照Sheet
            if (listNormal.size()>0){
                stepRow = setSheetRef(sheetRef,listNormal,"对账单",stepRow);
            }
            if (listCancel.size()>0){
                stepRow = setSheetRef(sheetRef,listCancel,"取消单",stepRow);
            }
            if (listHistory.size()>0){
                stepRow = setSheetRef(sheetRef,listHistory,"历史单",stepRow);
            }
            if(stepRow==0){
                return null;
            }
            // 触发公式自动计算
            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
            evaluator.evaluateAll();

            return wb;

        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public Workbook exportOrderStatement(OrderStatementParam param) {
        Workbook wb = new XSSFWorkbook();

        String sheetName = "对账明细";
        List<String> titles = new ArrayList<>();
        titles = Arrays.asList("订单时间", "运力商名称", "车队名称", "订单号", "第三方单号", "用车时间", "对账状态", "分账状态",  "订单金额",
                "商家实收金额", "平台优惠券", "商家优惠券", "乘客实付", "司机所得","司机所得比例", "司机信息", "订单信息",
                "起始地", "乘客姓名", "乘客电话", "账单生成时间","账单清算时间","备注");

        Sheet sheet = wb.createSheet(sheetName);

        // 表头——标题行
        Row row = sheet.createRow(0);

        // 标题行的单元格设置值
        for (int i = 0; i < titles.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(titles.get(i));
        }

        String idFranchiseeArr = franchiseeBiz.findIdFranArrByFinance(param.getIdUser());
        if (StringUtil.isNotBlank(idFranchiseeArr)) {
            param.setIdFranchiseeArr(idFranchiseeArr);
            param.setIdFranchisee(null);
        }

        List<OrderStatementVO> list = accountMapper.orderStatementSearchAll(param);

        for (int i = 0; i < list.size(); i++) {

            String checkStatus_dsp = Constants.Account_Check_Status.get(list.get(i).getCheckStatus());
            String divideAccountsStatus_dsp = Constants.Account_Divide_Status.get(list.get(i).getDivideAccountsStatus());
            String carpoolType_dsp = Constants.CarpoolType.get(list.get(i).getCarpoolType());
//            Float couponDiscount = list.get(i).getCouponDiscount();
//            if (couponDiscount==null){
//                couponDiscount = 0f;
//            }
//            String couponDiscount_dsp = String.format("%s元", couponDiscount);
            Float shopDiscount = list.get(i).getEstimatedMoney()-list.get(i).getOrderMoney();
            Float diRate = list.get(i).getDriverIncome() / list.get(i).getOrderMoney();
            String orderInfo = String.format("%s(%d)人", carpoolType_dsp,list.get(i).getPassengerNumber());
            String startEnd = String.format("%s-%s", list.get(i).getStartAddress(),list.get(i).getEndAddress());
            // 注意从第二行开始
            row = sheet.createRow(i + 1);
            // 单元格设置值，暂时先一个一个设吧
            int col = -1;
            col +=1; row.createCell(col).setCellValue(list.get(i).getOrderCreateTime());
            col +=1; row.createCell(col).setCellValue(list.get(i).getFranchiseeName());
            col +=1; row.createCell(col).setCellValue(list.get(i).getFleetName());
            col +=1; row.createCell(col).setCellValue(list.get(i).getIdOrder());
            col +=1; row.createCell(col).setCellValue(list.get(i).getExternalOrderId());
            col +=1; row.createCell(col).setCellValue(list.get(i).getAppointmentTime());
            col +=1; row.createCell(col).setCellValue(checkStatus_dsp);
            col +=1; row.createCell(col).setCellValue(divideAccountsStatus_dsp);
            col +=1; row.createCell(col).setCellValue(list.get(i).getEstimatedMoney());
            col +=1; row.createCell(col).setCellValue(list.get(i).getOrderMoney());
            col +=1; row.createCell(col).setCellValue(list.get(i).getCouponDiscount());
            col +=1; row.createCell(col).setCellValue(shopDiscount);
            col +=1; row.createCell(col).setCellValue(list.get(i).getPayMoney());
            col +=1; row.createCell(col).setCellValue(list.get(i).getDriverIncome());
            col +=1; row.createCell(col).setCellValue(diRate);
            col +=1; row.createCell(col).setCellValue(list.get(i).getDriverInfo());
            col +=1; row.createCell(col).setCellValue(orderInfo);
            col +=1; row.createCell(col).setCellValue(startEnd);
            col +=1; row.createCell(col).setCellValue(list.get(i).getPassengerName());
            col +=1; row.createCell(col).setCellValue(list.get(i).getPassengerMobile());
            col +=1; row.createCell(col).setCellValue(list.get(i).getCreateTime());
            col +=1; row.createCell(col).setCellValue(list.get(i).getSettlementTime());
            col +=1; row.createCell(col).setCellValue(list.get(i).getMemo());
        }

        return wb;
    }

    private int setMainSheet(Workbook wb,List<AccountMatch> list){
        int iBaseRow = 2 ;
        int iColFranchisee = 2;// 运力商
        int iColCity = 1;// 城市
        int iColOrderCount = 3;// 订单量
        int iColOrderMoney = 4;// 订单金额
        Row editRow =null;

        Sheet sheetSum = wb.getSheetAt(0);
        // 将第三行及以后的行都下移一行
        sheetSum.shiftRows(iBaseRow+1, sheetSum.getLastRowNum(), list.size());
        if (list.size()>0){
            for (int i = 0; i < list.size(); i++) {
                copyRow(wb,0,iBaseRow,iBaseRow+1+i);
            }
            for (int i = 0; i < list.size(); i++) {
                editRow = sheetSum.getRow(iBaseRow+i);
                //editRow.getCell(0).setCellValue(i+1);
                editRow.getCell(iColFranchisee).setCellValue(list.get(i).getNmFranchisee());
                editRow.getCell(iColCity).setCellValue(list.get(i).getCityFranchisee());
                editRow.getCell(iColOrderCount).setCellValue(list.get(i).getRowCount());
                editRow.getCell(iColOrderMoney).setCellFormula(list.get(i).formulaForMoney);
            }
        }
        return 0;
    }

    private int setSheetRef(Sheet sheetSum,List<AccountMatch> list,String type,int stepRow){
        int iBaseRow = 0 + stepRow;
        int iTitleRow = iBaseRow + 1;
        int iDataRow = iBaseRow + 2;
        int iColFranchisee = 1;// 运力商
        int iColCity = 0;// 城市
        int iColOrderCount = 2;// 订单量
        int iColOrderMoney = 3;// 订单金额
        Row editRow =null;

        if (list.size()>0){
            editRow = sheetSum.createRow(iBaseRow);
            editRow.createCell(0).setCellValue(type);
            editRow = sheetSum.createRow(iTitleRow);
            editRow.createCell(iColCity).setCellValue("城市");
            editRow.createCell(iColFranchisee).setCellValue("运力商");
            editRow.createCell(iColOrderCount).setCellValue("订单量");
            editRow.createCell(iColOrderMoney).setCellValue("订单金额");
            for (int i = 0; i < list.size(); i++) {
                editRow = sheetSum.createRow(iDataRow + i);
                editRow.createCell(iColFranchisee).setCellValue(list.get(i).getNmFranchisee());
                editRow.createCell(iColCity).setCellValue(list.get(i).getCityFranchisee());
                editRow.createCell(iColOrderCount).setCellValue(list.get(i).getRowCount());
                editRow.createCell(iColOrderMoney).setCellFormula(list.get(i).formulaForMoney);
            }
        }
        stepRow+=list.size();
        stepRow+=3;
        return stepRow;
    }

    private void setExcelSheetByFranchisee(Workbook wb,
                                           String nmFranchisee,
                                           OrderExportParam param,
                                           List<AccountMatch> listNormal,
                                           List<AccountMatch> listCancel,
                                           List<AccountMatch> listHistory){

        String formula = "";
        //单元格设置为数值类型，保留三位小数
        CellStyle styleMoney = wb.createCellStyle();
        //styleMoney.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00_ "));//下划线后面的空格不能省略
        styleMoney.setDataFormat(wb.createDataFormat().getFormat("0.00"));

        //单元格设置为整数
        CellStyle styleQty = wb.createCellStyle();
        //styleQty.setDataFormat(HSSFDataFormat.getBuiltinFormat("0_ "));
        styleQty.setDataFormat(wb.createDataFormat().getFormat("0"));

        List<String> orderStatus = new ArrayList<>();
        param.setHistoryFlg(0);
        // 正常对账单
        orderStatus.add(String.valueOf(Constants.OrderStatus.ORDER_STATUS_PAY_COMPLETED));
        param.setOrderStatus(orderStatus);
        List<ExportOrderVO> list1 = orderBiz.findExportOrderListDataWhenAccountMatch(param);
        if (list1.size()>0){
            String sheetName1 = String.format("%s(对账单)", nmFranchisee);
            Sheet sheet1 = wb.createSheet(sheetName1);
            setOrderExcelSheet(sheet1,list1,styleMoney,styleQty);
            formula = String.format("'%s'!Q%d", sheetName1,list1.size()+2);

            AccountMatch am = new AccountMatch();
            am.setNmFranchisee(list1.get(0).getFullName());
            am.setCityFranchisee(nmFranchisee);
            am.setRowCount(list1.size());
            am.setFormulaForMoney(formula);
            listNormal.add(am);

        }

        // 取消对账单
        orderStatus = new ArrayList<>();
        orderStatus.add(String.valueOf(Constants.OrderStatus.ORDER_STATUS_CANCEL));
        orderStatus.add(String.valueOf(Constants.OrderStatus.ORDER_STATUS_DRIVER_REJECTED));
        orderStatus.add(String.valueOf(Constants.OrderStatus.ORDER_STATUS_ADMIN_CANCELED));
        orderStatus.add(String.valueOf(Constants.OrderStatus.ORDER_STATUS_DRIVER_CANCELED));
        param.setOrderStatus(orderStatus);
        List<ExportOrderVO> list2 = orderMapper.findExportOrderListDataWhenAccountMatch(param);
        if (list2.size()>0){
            String sheetName2 = String.format("%s(取消单)", nmFranchisee);
            Sheet sheet2 = wb.createSheet(sheetName2);
            setOrderExcelSheet(sheet2,list2,styleMoney,styleQty);

            formula = String.format("'%s'!Q%d", sheetName2,list2.size()+2);

            AccountMatch am = new AccountMatch();
            am.setNmFranchisee(list2.get(0).getFullName());
            am.setCityFranchisee(nmFranchisee);
            am.setRowCount(list2.size());
            am.setFormulaForMoney(formula);
            listCancel.add(am);

        }

        // 历史对账单
        param.setHistoryFlg(1);
        orderStatus = new ArrayList<>();
        param.setOrderStatus(orderStatus);
        List<ExportOrderVO> list3 = orderMapper.findExportOrderListDataWhenAccountMatch(param);
        if (list3.size()>0){
            String sheetName3 = String.format("%s(历史单)", nmFranchisee);
            Sheet sheet3 = wb.createSheet(sheetName3);
            setOrderExcelSheet(sheet3,list3,styleMoney,styleQty);

            formula = String.format("'%s'!Q%d", sheetName3,list3.size()+2);

            AccountMatch am = new AccountMatch();
            am.setNmFranchisee(list3.get(0).getFullName());
            am.setCityFranchisee(nmFranchisee);
            am.setRowCount(list3.size());
            am.setFormulaForMoney(formula);
            listHistory.add(am);
        }

    }

    private Row copyRow(Workbook wb, int sheetIndex, int oldRowIndex, int newRowIndex) {
        Row oldRow = null;
        Row newRow = null;

        oldRow = wb.getSheetAt(sheetIndex).getRow(oldRowIndex);
        newRow = wb.getSheetAt(sheetIndex).createRow(newRowIndex);
        int oldLastCellIndex = oldRow.getLastCellNum();
        for (int j = 0; j < oldLastCellIndex; j++) {
            Cell cell = newRow.createCell(j);
            CellStyle newCellStyle = wb.createCellStyle();
            newCellStyle.cloneStyleFrom(oldRow.getCell(j).getCellStyle());
            cell.setCellStyle(oldRow.getCell(j).getCellStyle());
        }
        //newRow.getCell(0).setCellValue(newRowIndex);
        return newRow;
    }

    private void setOrderExcelSheet(Sheet sheet,List<ExportOrderVO> list,CellStyle styleMoney,CellStyle styleQty){
        List<String> titles = new ArrayList<>();
        titles = Arrays.asList("订单号", "运力商名称", "城市", "品牌", "下单时间", "用车时间", "起始地", "目的地", "乘客名称", "乘车人电话",  "下单人",
                "下单人电话", "司机信息", "车辆信息", "订单类型", "订单总额","商家实收金额", "附加金额", "优惠券金额","店铺优惠券金额",
                "实付金额", "司机应缴金额", "感谢费", "司机收入","比例", "订单花费时间", "行驶公里数", "等待时间", "下单来源","第三方渠道号",
                "对账结果", "支付方式", "订单状态", "备注", "销单原因");

        // 表头——标题行
        Row row = sheet.createRow(0);

        // 标题行的单元格设置值
        for (int i = 0; i < titles.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(titles.get(i));
        }

        Cell cell = null;
        double fEstimatedMoney = 0f;
        double fOrderMoney = 0f;
        double fPayMoney = 0f;
        double fDriverIncome = 0f;
        for (int i = 0; i < list.size(); i++) {

            Float driverIncomeRate = list.get(i).getDriverIncome() / list.get(i).getOrderMoney();
            // 注意从第二行开始
            row = sheet.createRow(i + 1);
            // 单元格设置值
            int col = -1;
            col +=1; row.createCell(col).setCellValue(list.get(i).getIdOrder());
            col +=1; row.createCell(col).setCellValue(list.get(i).getFullName());
            col +=1; row.createCell(col).setCellValue(HashMapConstants.idFranchiseeCityMap.get(list.get(i).getIdFranchisee()));
            col +=1; row.createCell(col).setCellValue(HashMapConstants.idBrandMap.get(list.get(i).getIdBrandHolder()));
            col +=1; row.createCell(col).setCellValue(list.get(i).getCreateTime().toString());
            col +=1; row.createCell(col).setCellValue(list.get(i).getAppointmentTime().toString());
            col +=1; row.createCell(col).setCellValue(list.get(i).getStartAddress());
            col +=1; row.createCell(col).setCellValue(list.get(i).getEndAddress());
            col +=1; row.createCell(col).setCellValue(list.get(i).getPassengerName());
            col +=1; row.createCell(col).setCellValue(list.get(i).getPassengerMobile());
            col +=1; row.createCell(col).setCellValue(list.get(i).getUserName());
            col +=1; row.createCell(col).setCellValue(list.get(i).getUserMobile());
            col +=1; row.createCell(col).setCellValue(list.get(i).getDriverInfo());
            col +=1; row.createCell(col).setCellValue(HashMapConstants.VehicleTypeMap.get(list.get(i).getIdVehicleType()));
            col +=1; row.createCell(col).setCellValue(Constants.OrderServiceType.get(list.get(i).getOrderType()));
            col +=1; cell = row.createCell(col); cell.setCellStyle(styleMoney);cell.setCellValue(NumUtil.getDouble(list.get(i).getEstimatedMoney()));
            col +=1; cell = row.createCell(col); cell.setCellStyle(styleMoney);cell.setCellValue(NumUtil.getDouble(list.get(i).getOrderMoney()));
            col +=1; cell = row.createCell(col); cell.setCellStyle(styleMoney);cell.setCellValue(NumUtil.getDouble(list.get(i).getAdditionalMoney()));
            col +=1; cell = row.createCell(col); cell.setCellStyle(styleMoney);cell.setCellValue(NumUtil.getDouble(list.get(i).getCouponDiscount()));
            col +=1; cell = row.createCell(col); cell.setCellStyle(styleMoney);cell.setCellValue(NumUtil.getDouble(list.get(i).getShopCouponDiscount()));
            col +=1; cell = row.createCell(col); cell.setCellStyle(styleMoney);cell.setCellValue(NumUtil.getDouble(list.get(i).getPayMoney()));
            col +=1; cell = row.createCell(col); cell.setCellStyle(styleMoney);cell.setCellValue(NumUtil.getDouble(list.get(i).getDriverIncome()));
            col +=1; cell = row.createCell(col); cell.setCellStyle(styleMoney);cell.setCellValue(NumUtil.getDouble(list.get(i).getOrderThanksFee()));
            col +=1; cell = row.createCell(col); cell.setCellStyle(styleMoney);cell.setCellValue(NumUtil.getDouble(list.get(i).getDriverIncome()));
            col +=1; cell = row.createCell(col); cell.setCellStyle(styleMoney);cell.setCellValue(NumUtil.getDouble(String.format("%.2f", driverIncomeRate)));
            col +=1; cell = row.createCell(col); cell.setCellStyle(styleQty);cell.setCellValue(NumUtil.getInteger(list.get(i).getOrderTime()));
            col +=1; cell = row.createCell(col); cell.setCellStyle(styleMoney);cell.setCellValue(NumUtil.getDouble(list.get(i).getOrderDistance()));
            col +=1; //等待时间
            col +=1; row.createCell(col).setCellValue(Constants.Order_Source.get(list.get(i).getOrderSource()));
            col +=1; row.createCell(col).setCellValue(list.get(i).getExternalOrderId());
            col +=1; row.createCell(col).setCellValue(Constants.Account_Check_Status.get(list.get(i).getCheckStatus()));
            col +=1; row.createCell(col).setCellValue(Constants.Payment_Type.get(list.get(i).getPaymentType()));
            col +=1; row.createCell(col).setCellValue(Constants.Order_Status.get(list.get(i).getStatus()));
            col +=1; row.createCell(col).setCellValue(list.get(i).getOrderMemo());
            col +=1;
            fEstimatedMoney += list.get(i).getEstimatedMoney();
            fOrderMoney += list.get(i).getOrderMoney();
            fPayMoney += list.get(i).getPayMoney();
            fDriverIncome += list.get(i).getDriverIncome();
        }

        int iMaxRowIdx = list.size()+1;
        row = sheet.createRow(iMaxRowIdx);
//        String formula = String.format("SUM(P2:P%d)", iMaxRowIdx);
//        row.createCell(15).setCellFormula(formula);
//        formula = String.format("SUM(Q2:Q%d)", iMaxRowIdx);
//        row.createCell(16).setCellFormula(formula);
//        formula = String.format("SUM(U2:U%d)", iMaxRowIdx);
//        row.createCell(20).setCellFormula(formula);
//        formula = String.format("SUM(X2:X%d)", iMaxRowIdx);
//        row.createCell(23).setCellFormula(formula);
//        DecimalFormat df = new DecimalFormat("#.00");
//        df.format(fOrderMoney);
        row.createCell(15).setCellValue( Math.round(fEstimatedMoney * 100.0) / 100.0);
        row.createCell(16).setCellValue(Math.round(fOrderMoney * 100.0) / 100.0);
        row.createCell(20).setCellValue(Math.round(fPayMoney * 100.0) / 100.0);
        row.createCell(23).setCellValue(Math.round(fDriverIncome * 100.0) / 100.0);

    }

}
